<%@page import="org.postgresql.Driver"%>
<html>
<body>
	<%@ page language="java" import="java.sql.*"%>
	<%!public String getQuarter(int i) {
		String quarter;
		switch (i) {
		case 1:
			quarter = "Fall";
			break;

		case 2:
			quarter = "Winter";
			break;

		case 3:
			quarter = "Spring";
			break;

		case 4:
			quarter = "Summer";
			break;
		default:
			quarter = "ERROR";
		}

		return quarter;
	}
	%>
	<%
	
	
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/cse132b",
					"cse132blogin", "1234567890");
			String action = request.getParameter("action");
			// Check if an insertion is requested
			
			if (action != null && action.equals("insert")) {
				// Create the prepared statement and use it top
				int NewID = 0;
				
				conn.setAutoCommit(false);
				
				PreparedStatement pstmt;
				if (request.getParameter("middlename").equals("")) {
					pstmt = conn
							.prepareStatement("INSERT INTO faculty (first_name,last_name,title) VALUES (?,?,?)", Statement.RETURN_GENERATED_KEYS);
				} else {
					pstmt = conn
							.prepareStatement("INSERT INTO faculty (first_name,last_name,title,middle_name) VALUES (?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
				}
				pstmt.setString(1, request.getParameter("firstname"));
				pstmt.setString(2, request.getParameter("lastname"));
				pstmt.setString(3, request.getParameter("title"));
				if (!request.getParameter("middlename").equals("")) {
					pstmt.setString(4, request.getParameter("middlename"));
				}
				int rowCount = pstmt.executeUpdate();
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					NewID = rs.getInt(1);
				}
				rs.close();
				pstmt.close();
				
				String arrClasses[] = request
						.getParameterValues("sltClasses");
				for (int i = 0; i < arrClasses.length; i++) {
					PreparedStatement statement1 = conn
							.prepareStatement("INSERT INTO teaches(faculty_id, section_id) VALUES (?,?)");
					statement1.setInt(1, NewID);
					statement1.setInt(2, Integer.parseInt(arrClasses[i]));
					statement1.executeUpdate();
					statement1.close();
				}
				
				conn.commit();
				conn.setAutoCommit(true);
				// Commit transaction
				out.println("Successfully made new student.<br/>");
			}
	%>


	<table border="1">
		<tr>
			<td valign="top" width="40%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>
				<table border="0">
					<tr>
						<td valign="top">Courses Entry Page</td>
					</tr>
					<tr>
						<table border="1">
							<tr>
								<th>First Name*</th>
								<th>Middle Name</th>
								<th>Last Name*</th>
								<th>Title*</th>
								<th>Teach*</th>
							</tr>
							<tr>
								<form action="faculty.jsp" method="get">
									<input type="hidden" value="insert" name="action">
									<td><input value="" name="firstname"></td>
									<td><input value="" name="middlename"></td>
									<td><input value="" name="lastname"></td>
									<td><input value="" name="title"></td>
									<td><select name="sltClasses" id="sltClasses" multiple>
											<%
												Statement stmt = conn.createStatement();
													ResultSet rsClass = stmt
															.executeQuery("SELECT * FROM sections LEFT JOIN classes ON (sections.class_id = classes.class_id) ORDER BY year, quarter DESC");
													while (rsClass.next()) {
											%>
											<option value="<%=rsClass.getInt("section_id")%>"><%=rsClass.getString("title")
							+ " ("
							+ getQuarter(Integer.valueOf(rsClass
									.getString("quarter"))) + " "
							+ rsClass.getString("year") + ") "%></option>
											<%
												}
													rsClass.close();
													stmt.close();
											%>
									</select></td>
									<td><input type="submit"></input></td>
								</form>
							</tr>
						</table>
					</tr>
				</table> <%
 	} catch (SQLException ex) {
 		out.print("SQLException: " + ex.getMessage() + "<br/>");
 		out.print("SQLState: " + ex.getSQLState() + "<br/>");
 		out.print("VendorError: " + ex.getErrorCode() + "<br/>");
 	}
 %> <%
 	
 %>
			</td>
		</tr>
	</table>
</body>
</html>